Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# IN Condition

An `in_condition` is a membership condition. It tests a value for membership in a list of values or subquery

in_conditions::=

Description of the illustration in_conditions.gif

expression_list::=

Description of the illustration expression_list.gif

If you use the upper form of the `in_condition` condition (with a single expression to the left of the operator), then you must use the upper form of `expression_list`. If you use the lower form of this condition (with multiple expressions to the left of the operator), then you must use the lower form of `expression_list`, and the expressions in each `expression_list` must match in number and datatype the expressions to the left of the operator.

See Also: "Expression Lists"

Table 7-12 lists the form of `IN` condition.

Table 7-12 IN Conditions

Type of Condition | Operation | Example
--- | --- | ---
    IN
| Equal-to-any-member-of test. Equivalent to `=ANY`. |
    SELECT * FROM employees
      WHERE job_id IN
      ('PU_CLERK','SH_CLERK');
    SELECT * FROM employees
      WHERE salary IN
      (SELECT salary
       FROM employees
       WHERE department_id =30);
    NOT IN
| Equivalent to !=`ALL`. Evaluates to `FALSE` if any member of the set is `NULL`. |
    SELECT * FROM employees
      WHERE salary NOT IN
      (SELECT salary
       FROM employees
      WHERE department_id = 30);
    SELECT * FROM employees
      WHERE job_id NOT IN
      ('PU_CLERK', 'SH_CLERK');

If any item in the list following a `NOT` `IN` operation evaluates to null, then all rows evaluate to `FALSE` or `UNKNOWN`, and no rows are returned. For example, the following statement returns the string '`True`' for each row:

    SELECT 'True' FROM employees
       WHERE department_id NOT IN (10, 20);

However, the following statement returns no rows:

    SELECT 'True' FROM employees
        WHERE department_id NOT IN (10, 20, NULL);

The preceding example returns no rows because the `WHERE` clause condition evaluates to:

    department_id != 10 AND department_id != 20 AND department_id != null

Because the third condition compares `department_id` with a null, it results in an `UNKNOWN`, so the entire expression results in `FALSE` (for rows with `department_id` equal to 10 or 20). This behavior can easily be overlooked, especially when the `NOT` `IN` operator references a subquery.

Moreover, if a `NOT` `IN` condition references a subquery that returns no rows at all, then all rows will be returned, as shown in the following example:

    SELECT 'True' FROM employees
       WHERE department_id NOT IN (SELECT 0 FROM DUAL WHERE 1=2);

Restriction on LEVEL in WHERE Clauses In a [`NOT`] `IN` condition in a `WHERE` clause, if the right-hand side of the condition is a subquery, you cannot use `LEVEL` on the left-hand side of the condition. However, you can specify `LEVEL` in a subquery of the `FROM` clause to achieve the same result. For example, the following statement is not valid:

    SELECT employee_id, last_name FROM employees
       WHERE (employee_id, LEVEL)
          IN (SELECT employee_id, 2 FROM employees)
       START WITH employee_id = 2
       CONNECT BY PRIOR employee_id = manager_id;

But the following statement is valid because it encapsulates the query containing the `LEVEL` information in the `FROM` clause:

    SELECT v.employee_id, v.last_name, v.lev
       FROM
          (SELECT employee_id, last_name, LEVEL lev
          FROM employees v
          START WITH employee_id = 100
          CONNECT BY PRIOR employee_id = manager_id) v
       WHERE (v.employee_id, v.lev) IN
          (SELECT employee_id, 2 FROM employees);